package nosql.query;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import nosql.MysqlConnector;
import nosql.property.Property;
import nosql.util.ComponentPair;
import nosql.util.Pair;
import nosql.util.QueryHistoryRecord;

import org.json.simple.parser.JSONParser;


/**
 * This class handle all the query related work, 
 * including find the records that satisfy the query,
 * record the query history, filter the query component.
 * @author Yexi Jiang <http://sites.google.com/site/yexijiang/>
 *
 */
public class NosqlQuery {
	private static JSONParser parser = new JSONParser();
	private MysqlConnector mysqlConnector;
	private List<ConditionalCriteria> listCriteria;
	private List<String> listPropertyList;
	
	
	public NosqlQuery(MysqlConnector connector){
		mysqlConnector = connector;
		listCriteria = new ArrayList<ConditionalCriteria>();
	}
	
	public void addCriteria(ConditionalCriteria criteria){
		listCriteria.add(criteria);
	}
	
	/**
	 * Retrive the result in form of map.
	 * @param entityId
	 * @return
	 * @throws SQLException
	 */
	private List<Pair<String, Map<String, Object>>> retrieveInMap(Set<String> entitySet, List<String> listProperty) throws SQLException{
		
		List<Pair<String, Map<String, Object>>> listResult = new ArrayList<Pair<String,Map<String,Object>>>();
		Statement stmt = mysqlConnector.getConnection().createStatement();
		for(String entityId : entitySet){
			Map<String, Object> map = new HashMap<String, Object>();
			Set<String> tables = mysqlConnector.getAllPropertyTableNameByEntityId(entityId);
			
			for (String table : tables) {
				if(!listProperty.contains(table.toLowerCase())){
					continue;
				}
				
				String sql = "select ref from " + table + " where entityId='"
						+ entityId + "'";
				ResultSet rs = stmt.executeQuery(sql);
				while (rs.next()) {
					String value = rs.getString("ref");
					
					try {
						double dValue = Double.parseDouble(value);
						map.put(table.toLowerCase(), dValue);
					} catch (NumberFormatException e) {
						if (value.equals("true"))
							map.put(table.toLowerCase(), true);
						else if (value.equals("false"))
							map.put(table.toLowerCase(), false);
						else
							map.put(table.toLowerCase(), value);
					}
					map.put(table.toLowerCase(), value);
				}
				rs.close();
			}
			listResult.add(new Pair<String, Map<String, Object>>(entityId, map));
		}
		stmt.close();
		
		return listResult;
	}
	
	/**
	 * Query result according to all criteria. 
	 * This method also insert the query history into database
	 * @return
	 * @throws CriteriaException 
	 * @throws SQLException 
	 */
	public QueryResult query(String queryStatement, List<String> listProperty) throws CriteriaException, SQLException{
		double start = System.currentTimeMillis();
		listPropertyList = listProperty;
		
		boolean allSimple = parseCriteria(queryStatement);
		List<Pair<String, Map<String, Object>>> resultList = new ArrayList<Pair<String, Map<String, Object>>>();
		
		if(allSimple == false){
			boolean firstSet = true;
			Set<String> validEntitySet = new HashSet<String>();
			try {
				for (ConditionalCriteria criteria : listCriteria) {
					if (firstSet == true) {
						firstSet = false;
						validEntitySet.addAll(criteria.getSatisfyEntities());
					} else
						validEntitySet.retainAll(criteria.getSatisfyEntities());
				}
				
				resultList = retrieveInMap(validEntitySet, listProperty);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		else{
			Set<String> validEntitySet = new HashSet<String>();
			String finalSQL = null;
			StringBuilder longSQLbuilder = new StringBuilder();
			if(listCriteria.size() > 1){
				ConditionalCriteria firstCriteria = listCriteria.get(0);
				String previousProperty = firstCriteria.getLeft();
				String firstLeft = firstCriteria.getLeft();
				String firstCondition = firstCriteria.getCondition();
				String firstRight = firstCriteria.getRight();
				String previousShortName = "t0";
				
				longSQLbuilder.append("select " + previousShortName + ".entityId from " + previousProperty + " as " + previousShortName + " inner join(");
				StringBuilder onPartBuilder = new StringBuilder();
				StringBuilder wherePartBuilder= new StringBuilder();
				int firstRightType = firstCriteria.getRightType();
				if(firstRightType == Property.NUMBER)
					wherePartBuilder.append(" where " + previousShortName + ".ref + 0 " + firstCondition + firstRight + " and ");
				else
					wherePartBuilder.append(" where " + previousShortName + ".ref " + firstCondition + firstRight + " and ");
				onPartBuilder.append(" on(");
				
				Map<String, String> tableNameMapping = new HashMap<String, String>();
				int idx = 0;
				for(int i = 1; i < listCriteria.size(); ++i){
					ConditionalCriteria criteria = listCriteria.get(i);
					String left = criteria.getLeft();
					String condition = criteria.getCondition();
					String right = criteria.getRight();
					String leftShortName = "t" + (++idx);
					
					tableNameMapping.put(left, leftShortName);
					
					longSQLbuilder.append(left + " as " + leftShortName);
					onPartBuilder.append(previousShortName + ".entityId=" + leftShortName + ".entityId");
					int rightType = criteria.getRightType();
					if(rightType == Property.NUMBER)
						wherePartBuilder.append(leftShortName + ".ref + 0 " + condition + right);
					else
						wherePartBuilder.append(leftShortName + ".ref " + condition + right);
					if(i < listCriteria.size() - 1){
						longSQLbuilder.append(",");
						onPartBuilder.append(" and ");
						wherePartBuilder.append(" and ");
						previousProperty = left;
						previousShortName = "t" + idx;
					}
				}
				longSQLbuilder.append(")");
				onPartBuilder.append(")");
				finalSQL = longSQLbuilder.toString() + onPartBuilder.toString() + wherePartBuilder.toString();

			}
			else if(listCriteria.size() == 1){
				ConditionalCriteria criteria = listCriteria.get(0);
				finalSQL = "select entityId from " + criteria.getLeft() 
						+ " where ref " + criteria.getCondition() + criteria.getRight();  
			}	
			else
				return new QueryResult(resultList);
			
			try{
				Statement stmt = mysqlConnector.getConnection().createStatement();
				System.out.println("SQL:" + finalSQL);
				ResultSet rsResultSet = stmt.executeQuery(finalSQL);
				while(rsResultSet.next()){
					validEntitySet.add(rsResultSet.getString("entityId"));
				}
				rsResultSet.close();
				resultList = retrieveInMap(validEntitySet, listProperty);
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		System.out.println("Query costs " + (System.currentTimeMillis() - start) + "ms");
		
		//	insert query history into database.
		insertHistory();
//		listPropertyList.clear();
//		listCriteria.clear();
		
		return new QueryResult(resultList);
	}
	
	/**
	 * Insert the query component history into table, including the filter co-occurrence history and condition co-occurrence history.
	 * @throws SQLException 
	 */
	protected void insertHistory() throws SQLException{
		Set<String> queryFilterComponentSet = new HashSet<String>();
		Set<String> queryConditionComponentSet = new HashSet<String>();
		queryFilterComponentSet.addAll(listPropertyList);
		
		for(ConditionalCriteria criteria : listCriteria){
			queryConditionComponentSet.add(criteria.getLeft());
		}
		
		//	generate components pair that would be added into history
		//	Map<ComponentPair, Type>,	typ=1 indicates filter, type=2 indicates condition
		Set<QueryHistoryRecord> newComponentPairSet = new HashSet<QueryHistoryRecord>();
		for(String first : queryFilterComponentSet){
			for(String second : queryFilterComponentSet){
				if(first.equals(second)){
					continue;
				}
				else{
					//	create pair in alphbetical order according to first and second component and insert into pair list
					if(first.compareTo(second) <= 0){
						newComponentPairSet.add(new QueryHistoryRecord(first, second, 1));
					}
					else{
						newComponentPairSet.add(new QueryHistoryRecord(second, first, 1));
					}
				}
			}
		}
		
		for(String first : queryConditionComponentSet){
			for(String second : queryConditionComponentSet){
				//	create pair in alphbetical order according to first and second component and insert into pair list
				if(first.compareTo(second) <= 0){
					newComponentPairSet.add(new QueryHistoryRecord(first, second, 2));
				}
				else{
					newComponentPairSet.add(new QueryHistoryRecord(second, first, 2));
				}
			}
		}
		
		//	load history pair from database
		//	Map<Pair<ComponentPair, Type>, Occurrence>
		Map<QueryHistoryRecord, Integer> pairHistory = new HashMap<QueryHistoryRecord, Integer>();
		String historyQueryStr = "select * from `*`;";
		Statement queryStmt = mysqlConnector.getConnection().createStatement();
		ResultSet queryResultSet = queryStmt.executeQuery(historyQueryStr);
		while(queryResultSet.next()){
			String first = queryResultSet.getString("firstProperty");
			String second = queryResultSet.getString("secondProperty");
			Integer occurrence = queryResultSet.getInt("occurrence");
			Integer type = queryResultSet.getInt("type");
			
			QueryHistoryRecord record = new QueryHistoryRecord(first, second, type);
			pairHistory.put(record, occurrence);
		}
		
		//	update pair history and write back to database
		Statement insertStmt = mysqlConnector.getConnection().createStatement();
		Statement updateStmt = mysqlConnector.getConnection().createStatement();
		
		System.out.println("Create prepared statement...");
		System.out.println("Size of newComponentSet:" + newComponentPairSet.size());
		System.out.println("Size of pairHistory:" + pairHistory.size());
		
		for(QueryHistoryRecord record : newComponentPairSet){
			Integer occurrence = null;
			
			
			if((occurrence = pairHistory.get(record)) != null){
				++occurrence;
				updateStmt.addBatch("UPDATE `*` SET occurrence=" + occurrence + " WHERE firstProperty='" + record.getFirst() + "' AND secondProperty='" + record.getSecond() + "' AND type=" + record.getType() +";");
				System.out.println("UPDATE `*` SET occurrence=" + occurrence + " WHERE firstProperty='" + record.getFirst() + "' AND secondProperty='" + record.getSecond() + "' AND type=" + record.getType() + ";");
			}
			else{
				occurrence = 1;
				insertStmt.addBatch("INSERT INTO `*`(firstProperty, secondProperty, type, occurrence) VALUES('" + record.getFirst() + "', '" + record.getSecond() + "'," + record.getType() + ", 1" + ");");
				System.out.println("INSERT INTO `*`(firstProperty, secondProperty, type, occurrence) VALUES('" + record.getFirst() + "', '" + record.getSecond() + "'," + record.getType() + ", 1" + ");");
			}
		}
//		System.out.println("Finished add batch.");
		
		updateStmt.executeBatch();
		insertStmt.executeBatch();
		mysqlConnector.getConnection().commit();
		
//		System.out.println("Update history finished.");
	}
	
	/**
	 * Parse the string into criteria.
	 * @param str
	 * @throws CriteriaException
	 */
	protected boolean parseCriteria(String str) throws CriteriaException{
		boolean allSimple = true;
		String[] criterias = str.trim().split(",");
		for(String criteria : criterias){
			ConditionalCriteria conditionalCriteria = ConditionalCriteria.createConditionalCriteria(criteria);
			if(conditionalCriteria.isSimple() == false)
				allSimple = true;
			if(conditionalCriteria != null){
				conditionalCriteria.setMysqlConnector(mysqlConnector);
				listCriteria.add(conditionalCriteria);
			}
		}
		return allSimple;
	}
	
}
